﻿using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Adams.Public.DataAccess
{
    public static class EidPrayers
    {
        public static Adams.Shared.Entities.EidPrayers.LocationCollection GetLocations()
        {
            var data = new Shared.Entities.EidPrayers.LocationCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT LocationID, Name, Street, Street2, City, State, ZipCode, Latitude, Longitude, DisplayOrder
FROM EidPrayers.Locations
ORDER BY DisplayOrder

SELECT *
FROM EidPrayers.Prayers
ORDER BY DisplayOrder
", CommandType.Text))
            {

                while (dr.Read())
                {
                    var location = new Shared.Entities.EidPrayers.Location();
                    location.Prayers = new Shared.Entities.EidPrayers.PrayerCollection();
                    location.ID = dr["LocationID"].DbInt();
                    location.Name = dr["Name"].DbString();
                    location.Street = dr["Street"].DbString();
                    location.Street2 = dr["Street2"].DbString();
                    location.City = dr["City"].DbString();
                    location.State = dr["State"].DbString();
                    location.ZipCode = dr["ZipCode"].DbString();
                    location.Latitude = dr["Latitude"].DbDecimal();
                    location.Longitude = dr["Longitude"].DbDecimal();
                    location.DisplayOrder = dr["DisplayOrder"].DbInt();
                    data.Add(location);
                }

                dr.NextResult();

                while (dr.Read())
                {
                    var prayer = new Shared.Entities.EidPrayers.Prayer();
                    prayer.ID = dr["PrayerID"].DbInt();
                    prayer.Name = dr["Name"].DbString();
                    prayer.Time = dr["Time"].DbString();
                    prayer.DisplayOrder = dr["DisplayOrder"].DbInt();
                    data.Where(l => l.ID == dr["LocationID"].DbInt()).FirstOrDefault().Prayers.Add(prayer);
                }

                return data;
            }
        }

        public static Adams.Shared.Entities.EidPrayers.Location GetLocation(Adams.Public.DataTransfer.EidPrayers.GetLocationInput input)
        {
            var data = new Shared.Entities.EidPrayers.Location();
            data.Prayers = new Shared.Entities.EidPrayers.PrayerCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
DECLARE @LocationID int
SELECT @LocationID = LocationID FROM EidPrayers.Locations WHERE Name = @Location

SELECT LocationID, Name, Street, Street2, City, State, ZipCode, Latitude, Longitude, DisplayOrder, LongNote
FROM EidPrayers.Locations
WHERE LocationID = @LocationID

SELECT *
FROM EidPrayers.Prayers
WHERE LocationID = @LocationID
ORDER BY DisplayOrder
", CommandType.Text,
    DB.CreateParameter("@Location", input.Location)))
            {

                dr.Read();
                data.ID = dr["LocationID"].DbInt();
                data.Name = dr["Name"].DbString();
                data.Street = dr["Street"].DbString();
                data.Street2 = dr["Street2"].DbString();
                data.City = dr["City"].DbString();
                data.State = dr["State"].DbString();
                data.ZipCode = dr["ZipCode"].DbString();
                data.Latitude = dr["Latitude"].DbDecimal();
                data.Longitude = dr["Longitude"].DbDecimal();
                data.LongNote = dr["LongNote"].DbString();
                data.DisplayOrder = dr["DisplayOrder"].DbInt();

                dr.NextResult();

                while (dr.Read())
                {
                    var prayer = new Shared.Entities.EidPrayers.Prayer();
                    prayer.ID = dr["PrayerID"].DbInt();
                    prayer.Name = dr["Name"].DbString();
                    prayer.Time = dr["Time"].DbString();
                    prayer.DisplayOrder = dr["DisplayOrder"].DbInt();
                    data.Prayers.Add(prayer);
                }

                return data;
            }
        }
    }
}
